package com.wwwc.index.ejb.language;

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import java.rmi.RemoteException;

public class LanguageBean implements SessionBean {
    private Connection con;

    public void ejbCreate() throws CreateException {}
    public void ejbRemove() {}
    public void ejbActivate() {}
    public void ejbPassivate() {}
    public void setSessionContext(SessionContext sc) {}

    public int tableExists(String table_name) {
	int found = 0;
        try {  
	    makeConnection();
	    String query = "SELECT TABLE_NAME FROM SYSTEM_TABLES WHERE TABLE_NAME='"+table_name+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    while (rs.next()) {
		if((rs.getString(1)).equals(table_name)) {
		    found = 1;
		    break;
		}
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    found = -1;
	    System.out.println("LanguageBean:tableExists:Error:"+e.getMessage());
        } 
	return found;
    } 

    public boolean createLanguageSupportTable() {
	String query = null;
	PreparedStatement prepStmt = null;
        try {  
	    makeConnection();
	    query = "CREATE TABLE WWWC_LANGUAGE_SUPPORT ("+
		"NAME VARCHAR (100) PRIMARY KEY,"+
		"ABBRAVIATION VARCHAR (10),"+
		"ACTIVE INTEGER);";
	    prepStmt = con.prepareStatement(query);
	    prepStmt.execute();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("LanguageBean:createLanguagerSupportTable:Error:"+e+"\n"+query);
	    return false;
        } 

	try {  
	    query = "CREATE TABLE WWWC_LANGUAGE_TRANS ("+
		"KEY VARCHAR_IGNORECASE (250) PRIMARY KEY, ENGLISH VARCHAR_IGNORECASE (250));";
	    makeConnection();
	    prepStmt = con.prepareStatement(query);
	    prepStmt.execute();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("LanguageBean:createLanguagerTRANS:Error:"+e+"\n"+query);
	    return false;
        } 
	return true;
    } 

    public boolean insertDefaultSupportedLanguage() {
	String query = null;

        try {  
	    makeConnection();
	    PreparedStatement prepStmt = null;
	    query = "INSERT INTO WWWC_LANGUAGE_SUPPORT ("+
		"NAME,"+
		"ABBRAVIATION,"+
		"ACTIVE) VALUES (?, ?, ?);";

	    prepStmt = con.prepareStatement(query);

	    Locale locs[] = (new Locale("English")).getAvailableLocales();
	    for (int i=0; i<locs.length; i++) {
		if(locs[i].getDisplayCountry().length()==0) {
		    prepStmt.setString(1, locs[i].getDisplayLanguage());
		    prepStmt.setString(2, locs[i].getLanguage());
		    prepStmt.setInt(3, 0);
		    prepStmt.executeUpdate();
		}
	    }

	    prepStmt.setString(1, "Vietnamese");
	    prepStmt.setString(2, "vi");
	    prepStmt.setInt(3, 0);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("LanguageBean:insertDefaultSupportedLanguage:Error:"+e+"\n"+query);
	    return false;
        } 
	return true;
    } 
   
    public boolean insertDefaultLanguage() {
	String query = null;

        try {  
	    makeConnection();
	    query = ""+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('January','January');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('February','February');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('March','March');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('April','April');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('May','May');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('June','June');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('July','July');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('August','August');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('September','September');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('October','October');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('November','November');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('December','December');"+

		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Add','Add');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Admin','Admin');"+	
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Birthday','Birthday');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Blogs','Blogs');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Current E-mail','Current E-mail');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Country','Country');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Day','Day');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Education','Education');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('High School','High School');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('College-2years','College (2 years)');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Bachelor','Bachelor');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Master','Master');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('PHD','Ph.D/Dr.');"+

		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Error','Error');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Female','Female');"+    
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Field','Field');"+    
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Forums','Forums');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Gender','Gender');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Image','Image');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Inbox','Inbox');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Join Us','Join Us');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Male','Male');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Mail','Mail');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Manager(s)','Manager(s)');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Month','Month');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Myself Only','Myself Only');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('No','No');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('On Top','On Top');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Other','Other');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Password','Password');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Page(s)','Page(s)');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('PageNo','Page No.');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Reset','Reset');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Records','Record(s)');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Reply','Reply');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Re-type password','Re-type Password');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Search','Search');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Sign In','Sign In');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Sign Off','Sign Off');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Sent','Sent');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Submit','Submit');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Trash','Trash');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Title Is Required','Title is required');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Title','Title');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Total','Total');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Total Found','Total Found');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Text','Text');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Username','Username');"+		
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Yes','Yes');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Year','Year');"+
		"INSERT INTO WWWC_LANGUAGE_TRANS (KEY, ENGLISH) VALUES('Zip Code','Zip/Postal code');";

	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("LanguageBean:insertDefaultLanguage:Error:"+e+"\n"+query);
	    return false;
        } 
	return true;
    } 

    public boolean dropTable(String table_name) {
	String query = null;

        try {  
	    makeConnection();
	    query = "DROP TABLE "+table_name+";";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("LanguageBean:dropTable:Error:"+e+"\n"+query);
	    return false;
        } 
	return true;
    } 

    public ArrayList getSupportedLanguageList() {
	ArrayList a = null;
	ArrayList aa = new ArrayList();
        try {
	    makeConnection();
	    String query = "SELECT NAME, ABBRAVIATION FROM WWWC_LANGUAGE_SUPPORT WHERE "+
		"ACTIVE >0 ORDER BY ACTIVE;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    while (rs.next()) {
		a = new ArrayList();
		a.add(rs.getString(1));
		a.add(rs.getString(2));
		aa.add(a);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getLanguageList:error:" + e.getMessage());
        } 
        return aa;
    } 

    public ArrayList getAllLanguageList() {
	ArrayList a = null;
	ArrayList aa = new ArrayList();
        try {
	    makeConnection();
	    String query = "SELECT NAME, ABBRAVIATION, ACTIVE FROM WWWC_LANGUAGE_SUPPORT ORDER BY NAME;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    while (rs.next()) {
		a = new ArrayList();
		a.add(rs.getString(1));
		a.add(rs.getString(2));
		a.add(rs.getString(3));
		aa.add(a);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getAllLanguageList:error:" + e.getMessage());
        } 
        return aa;
    } 

    private String getLanguageTransName(String lid) {
	if(lid == null) {
	    return null;
	}

	String name = null;
        try {
	    makeConnection();
	    String query = "SELECT NAME FROM WWWC_LANGUAGE_SUPPORT WHERE "+
		"ABBRAVIATION='"+lid+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	   
	    if (rs.next()) {
		name = rs.getString(1);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("EJB:LanguageBean:getLanguageTransName:error:" + e.getMessage());
        } 
        return name;
    } 

    public Hashtable getLanguageHashtable(String lid) {
	if(lid == null) {
	    return null;
	}

	Hashtable mhs = null;
        try {
	    String name = getLanguageTransName(lid);
	    if(name == null) {
		return null;
	    }

	    makeConnection();
	    
	    String query = "SELECT KEY, "+name+" FROM WWWC_LANGUAGE_TRANS;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    
	    mhs = new Hashtable();
	    while (rs.next()) {
		mhs.put(rs.getString(1), rs.getString(2));
	    }
	    
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getLanguageList:error:" + e.getMessage());
        } 
        return mhs;
    } 

    public Hashtable getSupportedLanguageTrans(ArrayList aa) {
	if(aa == null || aa.isEmpty()) {
	    return null;
	}

	Hashtable slhs = null;
        try {
	    String query = null;
	    PreparedStatement prepStmt = null;
	    ResultSet rs = null;
	    
	    slhs = new Hashtable();
	    
	    Iterator it = aa.iterator();
	    String col_name = null;
	    ArrayList a = null;
	    
	    while(it.hasNext()) {
		a = (ArrayList)it.next();
		col_name = (String)a.get(0);
		makeConnection();
		query = "SELECT KEY, "+col_name.toUpperCase()+" FROM WWWC_LANGUAGE_TRANS WHERE KEY='"+col_name+"';";
		prepStmt = con.prepareStatement(query);
		rs = prepStmt.executeQuery();
		
		if (rs.next()) {
		    slhs.put(rs.getString(1), rs.getString(2));
		}

		rs.close();
		prepStmt.close();
		releaseConnection();
	    }
        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getSupportedLanguageTrans:error:" + e.getMessage());
        } 

        return slhs;
    } 

    private boolean languageColumnExists (String name){
	String query = "SELECT LIMIT 0 1 * FROM WWWC_LANGUAGE_TRANS;";
	boolean exists = false;

	try {	
	    makeConnection();
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    for(int m = 1; m <= colums; m++) {
		if(rsmd.getColumnName(m).equals(name)) {
		    exists = true;
		    break;
		}
	    }
	    
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(Exception e) {
	    System.out.println("LanguageBean:languageColumnExists:Error:"+e);
	}
        return exists;
    }

    private ArrayList getSupportedLanguageColumnNames (){
	String query = "SELECT LIMIT 0 1 * FROM WWWC_LANGUAGE_TRANS;";
	ArrayList a = new ArrayList();

	try {	
	    makeConnection();
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();
	    String name = null;

	    for(int m = 1; m <= colums; m++) {
		name = rsmd.getColumnName(m);
		a.add(name);
	    }
	    
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(Exception e) {
	    System.out.println("LanguageBean:getSupportedLanguageColumnNames:Error:"+e);
	}
        return a;
    }

    public ArrayList getLanguageTransArrayList(String name) {
	if(name == null) {
	    System.out.println("LanguageBean:getLanguageTransArrayList:Error:name==null");
	    return null;
	}

	name = name.toUpperCase();

	ArrayList aa = null;
        String query = null;
	PreparedStatement prepStmt = null;
	ResultSet rs = null;

	try {
	    if(!languageColumnExists(name)) {
		makeConnection();
		query = "ALTER TABLE WWWC_LANGUAGE_TRANS ADD COLUMN "+name+" VARCHAR_IGNORECASE (250);";
		prepStmt = con.prepareStatement(query);
		prepStmt.executeUpdate();
		prepStmt.close();
		releaseConnection();

		Hashtable ehs =  getLanguageHashtable("en");
		if(ehs != null && !ehs.isEmpty()) {
		    makeConnection();
		    query = "UPDATE WWWC_LANGUAGE_TRANS SET "+name+"=? WHERE KEY=?;";
		    prepStmt = con.prepareStatement(query);
		    String key = null;
		    Enumeration enum = ehs.keys();
		    while(enum.hasMoreElements()) {
			key = (String)enum.nextElement();
			prepStmt.setString(1, (String)ehs.get(key));
			prepStmt.setString(2, key);
			prepStmt.executeUpdate();
		    }

		    prepStmt.close();
		    releaseConnection();
		}
	    }

	    makeConnection();
	    query = "SELECT KEY, ENGLISH, "+name+" FROM WWWC_LANGUAGE_TRANS;";
	    prepStmt = con.prepareStatement(query);
	    rs = prepStmt.executeQuery();

	    aa = new ArrayList();
	    ArrayList a = null;
	    while (rs.next()) {
		a = new ArrayList();
		a.add(rs.getString(1));
		a.add(rs.getString(2));
		a.add(rs.getString(3));
		aa.add(a);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getLanguageList:error:" + e.getMessage());
        } 
        return aa;
    } 

    public boolean addLanguageTrans(String key) {
	ArrayList a = getSupportedLanguageColumnNames();
	if(a == null || a.isEmpty()) {
	    return false;
	}
	if(key == null || key.length()==0) {
	    return false;
	}
	if(key.indexOf("'") != -1) {
	    key = key.replaceAll("'", "''");
	}

	StringBuffer sbf1 = new StringBuffer("INSERT INTO WWWC_LANGUAGE_TRANS (");
	StringBuffer sbf2 = new StringBuffer("VALUES (");

	Iterator it = a.iterator();
	while(it.hasNext()){
	    sbf1.append(it.next()+",");
	    sbf2.append("'"+key+"',");
	}

	String query1 = sbf1.toString();
	query1 = query1.substring(0, query1.length()-1);

	String query2 = sbf2.toString();
	query2 = query2.substring(0, query2.length()-1);

	String query = query1+")"+query2+");";

        try {  
	    makeConnection();
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.executeUpdate();
	    
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("LanguageBean:addLanguageTrans:Error:"+e+"\n"+query);
	    return false;
        } 
	return true;
    }

    public int addLanguageSupport(String name) {
	int result = 0;
	if(name == null) {
	    return 0;
	}

	try {
	    makeConnection();
	    String query1 = "SELECT MAX(ACTIVE) AS next_lid FROM WWWC_LANGUAGE_SUPPORT;";
	    PreparedStatement prepStmt = con.prepareStatement(query1);
	    ResultSet rs =  prepStmt.executeQuery();
	    if(rs.next()) {
		result = rs.getInt("next_lid");
	    }
	    prepStmt.close();
	    releaseConnection();

	    if(result == 0) {
		result = 10;
	    }
	    else {
		result = result+1;
	    }
	    makeConnection();
	    String query2 = "UPDATE WWWC_LANGUAGE_SUPPORT SET ACTIVE="+result+" WHERE NAME='"+name+"';";
	    prepStmt = con.prepareStatement(query2);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:addLanguageSupport:Error:" + e.getMessage());
	     return -1;
        } 
        return result;
    } 

    public int translateLanguage(String col_name, String key, String new_value) {
	int result = 0;
	if(col_name == null || col_name.length()==0 || key == null || key.length()==0 || new_value == null || new_value.length()==0) {
	    System.out.println("LanguageBean:translateLanguage:Error:101");
	    return 0;
	}

	try {
	    makeConnection();
	    String query = "UPDATE WWWC_LANGUAGE_TRANS SET "+col_name.toUpperCase()+"= ? WHERE KEY=?;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setString(1, new_value);
	    prepStmt.setString(2, key);
	    result = prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("EJB:LanguageBean:translateLanguage:Error:" + e.getMessage());
        } 
        return result;
    } 

    public int removeLanguageSupport(String name) {
	int result = 0;
	if(name == null) {
	    return 0;
	}

	try {
	    makeConnection();
	    String query = "UPDATE WWWC_LANGUAGE_SUPPORT SET ACTIVE=0 WHERE NAME='"+name+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    result = prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:removeLanguageSupport:Error:" + e.getMessage());
        } 
        return result;
    } 

    public int setDefaultLanguage(String name) {
	int result = 0;
	if(name == null) {
	    return 0;
	}

	String dname = getDefaultLanguageName();
	if(dname == null || dname.equals(name)) {
	    return 0;
	}

	int active = getLanguageActive(name);
	if(active == -1) {
	    return 0;
	}

	try {
	    makeConnection();
	    String query = "UPDATE WWWC_LANGUAGE_SUPPORT SET ACTIVE=? WHERE NAME=?;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setInt(1, 10);
	    prepStmt.setString(2, name);
	    prepStmt.executeUpdate();

	    prepStmt.setInt(1, active);
	    prepStmt.setString(2, dname);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:setDefaultLanguage:Error:" + e.getMessage());
	     result = -1;
        } 
        return result;
    } 

    private String getDefaultLanguageName () {
	String name = null;

        try {
	    makeConnection();
	    String query = "SELECT NAME FROM WWWC_LANGUAGE_SUPPORT WHERE ACTIVE = 10;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if (rs.next()) {
		name = rs.getString(1);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getDefaultLanguageName:Error:" + e.getMessage());
        } 
        return name;
    } 

    private int getLanguageActive (String name) {
	int active = -1;

        try {
	    makeConnection();
	    String query = "SELECT ACTIVE FROM WWWC_LANGUAGE_SUPPORT WHERE NAME = '"+name+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if (rs.next()) {
		active = rs.getInt(1);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getLanguageActive:Error:" + e.getMessage());
        } 
        return active;
    } 

    public String getDefaultLanguageAbbreviation() {
	String dl = "en";

        try {
	    makeConnection();
	    String query = "SELECT ABBRAVIATION FROM WWWC_LANGUAGE_SUPPORT WHERE ACTIVE = 10;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if (rs.next()) {
		dl = rs.getString(1);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:getDefaultLanguageAbbreviation:Error:" + e.getMessage());
        } 
        return dl;
    } 

    public boolean languageIsSupported(String lid) {
	int active = 0;

        try {
	    makeConnection();
	    String query = "SELECT ACTIVE FROM WWWC_LANGUAGE_SUPPORT WHERE ABBRAVIATION = '"+lid+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if (rs.next()) {
		active = rs.getInt(1);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:LanguageBean:languageIsSupported:Error:" + e.getMessage());
        } 
	if(active >= 10) {
	    return true;
	}

        return false;
    } 

    ////////////////////////////////////////////////////////////////////
    // Database Routines
    ////////////////////////////////////////////////////////////////////

    private void makeConnection() {
        try {
	    InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/DefaultDS");
            con =  ds.getConnection();
        } catch (Exception e) {
	    releaseConnection();
	    System.out.println("EJB:LanguageBean: makeConnection error:" + e.getMessage());
        }
    }
  
    private void releaseConnection() {
        try {
            con.close();
        } catch (SQLException e) {
	    System.out.println("LanguageBean: Reaease connect error: " + e.getMessage());
        }
    }

    private void rollback() {
	PreparedStatement prepStmt= null;
	try {	
	    makeConnection();
	    prepStmt =  con.prepareStatement("ROLLBACK");
	    prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    releaseConnection();
	    System.out.println("Rollback SQLException="+e);
	}
	catch(Exception e) {
	    releaseConnection();
	    System.out.println("Rollback Exception="+e);
	}
    }
}
